package com.soft.blog_project.controller;

import com.soft.blog_project.pojo.*;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;

@Api(tags = "大屏模块")
@RestController
@CrossOrigin(origins="*")
public class BigData {
    @Autowired(required=false)//初始化对象(这一句可以解决17行jdbc红色)
    private JdbcTemplate jdbc;
    //用户的地区统计
    //http://localhost:9090/citycount
    @ApiOperation(value = "地区的用户数量统计",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "统计并返回各地区的用户数量，code:200表示成功，201表示异常")
    @GetMapping("/citycount")
    public Result CityCount(){
        Result result=new Result();
        try{
            List<CityCount> citynum=jdbc.query("SELECT user.city city_name, COUNT(User_id) user_num FROM user GROUP BY user.city",
                    new BeanPropertyRowMapper<>(CityCount.class));
            result.setResult(citynum);
            result.setCode(200);
        }catch (DataAccessException e){
            e.printStackTrace();
            result.setResult("Failed"+e.getMessage());
            result.setCode(201);
        }
        return result;
    }

    //用户的年龄段统计
    //http://localhost:9090/agecount
    @ApiOperation(value = "基于年龄区间的用户数量统计",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "返回各年龄区间的用户数量,code:200表示成功，201表示异常")
    @GetMapping("/agecount")
    public Result AgeCount(){
        Result result=new Result();
        try{
            List<AgeCount> agenum=jdbc.query("SELECT \n" +
                            "  CASE \n" +
                            "    WHEN user.age BETWEEN  0 AND  19 THEN '0-19岁'\n" +
                            "    WHEN user.age BETWEEN 20 AND 39 THEN '20-39岁'\n" +
                            "    WHEN user.age BETWEEN 40 AND 59 THEN '40-59岁'\n" +
                            "    ELSE '60岁以上'\n" +
                            "  END AS age_group,\n" +
                            "  COUNT(User_id) AS num\n" +
                            "FROM \n" +
                            "  user\n" +
                            "GROUP BY \n" +
                            "  age_group\n" +
                            "ORDER BY\n" +
                            "  age_group",
                    new BeanPropertyRowMapper<>(AgeCount.class));
            result.setResult(agenum);
            result.setCode(200);
        }catch (DataAccessException e){
            e.printStackTrace();
            result.setResult("Failed"+e.getMessage());
            result.setCode(201);
        }
        return result;
    }

    //用户性别统计
    @ApiOperation(value = "基于性别的用户数量统计",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "返回不同性别的用户数量,code:200表示成功，201表示异常")
    @GetMapping("/sexCount")
    public Result sexCount(){
        Result result = new Result();
        try {
            List<SexCount> tjs = jdbc.query("select sex gender,COUNT(User_id) num from user GROUP BY user.sex",
                    new BeanPropertyRowMapper<>(SexCount.class));
            result.setResult(tjs);
            result.setCode(200);
        } catch (DataAccessException e) {
            result.setResult("失败"+e.getMessage());
            result.setCode(201);
        }
        return result;
    }

    //博客类型统计
    //http://localhost:9090/blogtypecount
    @ApiOperation(value = "基于类别的博客数量统计",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "返回不同类别的博客数量,code:200表示成功，201表示异常")
    @GetMapping("/blogtypecount")
    public Result BlogTypeCount(){
        Result result=new Result();
        try{
            List<BlogTypeCount> typenum=jdbc.query("SELECT type_name blogtype, COUNT(Blog_id) num " +
                            "FROM blog inner join type on blog.type_id=type.type_id " +
                            "GROUP BY type_name",
                    new BeanPropertyRowMapper<>(BlogTypeCount.class));
            result.setResult(typenum);
            result.setCode(200);
        }catch (DataAccessException e){
            e.printStackTrace();
            result.setResult("Failed"+e.getMessage());
            result.setCode(201);
        }
        return result;
    }

    //博客总数量统计
    //http://localhost:9090/BlogNum
    @ApiOperation(value = "博客总数量统计",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示成功，201表示异常")
    @GetMapping("/BlogNum")
    public Result BlogNum(){
        Result result = new Result();
        try {
            result.setCode(200);
            result.setResult(
                    jdbc.queryForObject("SELECT COUNT(*) FROM blog",
                            Integer.class));
            return result;
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("出现异常"+e.getMessage());
            return result;
        }
    }

    //用户总数量统计
    //http://localhost:9090/UserNum
    @ApiOperation(value = "用户总数量统计",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示成功，201表示异常")
    @GetMapping("/UserNum")
    public Result UserNum(){
        Result result = new Result();
        try {
            result.setCode(200);
            result.setResult(
                    jdbc.queryForObject("SELECT COUNT(*) FROM user",
                            Integer.class));
            return result;
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("出现异常"+e.getMessage());
            return result;
        }
    }

    //今日发布博客统计
    //http://localhost:9090/TodayBlog
    @ApiOperation(value = "今日发布博客统计",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示成功，201表示异常")
    @GetMapping("/TodayBlog")
    public Result TodayBlog(){
        Result result = new Result();
        try {
            LocalDateTime currentTime = LocalDateTime.now();
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
            String formattedTime = currentTime.format(formatter);
            result.setCode(200);
            result.setResult(
                    jdbc.queryForObject("SELECT COUNT(*) FROM blog where time=?",
                            Integer.class,formattedTime));
            return result;
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("出现异常"+e.getMessage());
            return result;
        }
    }

    // 获取当前时间
    //http://localhost:9090/getCurrentTime
    @ApiOperation(value = "获取当前时间",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示成功，201表示异常")
    @GetMapping("/getCurrentTime")
    public String getCurrentTime() {
        LocalDateTime currentTime = LocalDateTime.now();

        // 格式化时间，这里我们使用了 "yyyy-MM-dd HH:mm:ss" 格式，您可以根据需求修改
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String formattedTime = currentTime.format(formatter);

        return formattedTime;
    }

    @ApiOperation(value = "博客发布时间统计",
            protocols = "http",
            httpMethod = "GET",
            consumes = "application/json",
            response = Result.class,
            notes = "code:200表示成功，201表示异常")
    @GetMapping("/timeBlog")
    public Result timeBlog(){
        Result result = new Result();
        try {
            List<timeBlog> timeBlogs = jdbc.query("select time, count(*) as blognum from blog group by time order by time desc;", new BeanPropertyRowMapper<>(timeBlog.class));
            result.setCode(200);
            result.setResult(timeBlogs);
            return result;
        } catch (DataAccessException e) {
            e.printStackTrace();
            result.setCode(201);
            result.setResult("异常"+e.getMessage());
            return result;
        }
    }


}
